﻿<AddDataFormTemplateSpec 
	xmlns="bb_appfx_adddataformtemplate"
	xmlns:c="bb_appfx_commontypes" 
	ID="532a22ee-5cd2-440b-afa6-0a708dd76a6b" 
	Name="Revenue Add Data Form Custom"
	Description="Revenue Add Form - Custom" 
	Author="Blackbaud Product Development"
	DataFormInstanceID="be72654a-510e-4746-a8b2-1980c08b5563" 
	RecordType="Revenue"
	c:SecurityUIFolder="Service Bus Adapter"
	>
    
	<SPDataForm>
		<SaveImplementation SPName="USP_DATAFORMTEMPLATE_ADD_REVENUE_CUSTOM">
			<c:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USP_DATAFORMTEMPLATE_ADD_REVENUE_CUSTOM(
  @ID uniqueidentifier = null output
 ,@CHANGEAGENTID uniqueidentifier = null
 ,@ConstituentID uniqueidentifier = null
 ,@RevenueDate datetime = null
 ,@PostDate datetime = null
 ,@Amount money = 0.00
 ,@Description nvarchar(255) = ''
 ,@LookupID nvarchar(255) = ''
 ,@Designation nvarchar(255) = ''
 ,@SubDesignation nvarchar(255) = ''
 ,@ReceiptDate datetime = null
 ,@PaymentMethod nvarchar(255) = ''
 ,@PledgeId nvarchar(255) = ''
 ,@ErrorMessages nvarchar(max) = null
)
as
begin
  set @ErrorMessages = '';
  
  begin try
    --BEGIN: boilerplate setup
    set @id = isnull(@id,newid());
    if @CHANGEAGENTID is null begin
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
    end;
    declare @CURRENTDATE datetime = getdate();
    --END: boilerplate setup

    declare @converted_designationid uniqueidentifier = null;
    declare @PledgeGuid uniqueidentifier = null;
      
    if @pledgeid <> '' begin
      exec USR_USP_PARSEFORMATTEDPLEDGEID @PledgeID, @PledgeGuid output, @converted_designationid output;
    end;

    declare @DesignationID uniqueidentifier = (SELECT ID FROM DESIGNATION WHERE USERID = @Designation);

    IF @DesignationID IS NULL BEGIN
      SET @DesignationID = (SELECT ID FROM DESIGNATION WHERE USERID = @SubDesignation)
    END;

    IF @DesignationID IS NULL BEGIN
      --this is going to take us to the catch block, where the error ends up inside @ErrorMessages
      set @ErrorMessages = @ErrorMessages + ' / Could not get DESIGNATION for USERID / ';
    END
    
    --if the DesignationID they passed us isnt valid for this pledge, use the one we parsed out of @PledgeID
    select
      @DesignationID = 
      case when exists(select * from revenuesplit where revenueid = @PledgeGuid and designationid = @DesignationID)
           then @DesignationID  --no change
           else @converted_designationid  --use the alternate one
           end;

    IF @DesignationID IS NULL BEGIN
      set @ErrorMessages = @ErrorMessages + ' / Designation did not match pledge / ';
    end
    else begin
      set @ErrorMessages = '';
    END;

    DECLARE @ReceiptNumber int = (SELECT COALESCE((SELECT MAX(RECEIPTNUMBER) FROM REVENUERECEIPT), 0) + 1);
    DECLARE @PaymentMethodCode int = 0;

    select
      @PaymentMethodCode =
        case when @paymentmethod = 'Cash'         then 0
             when @paymentmethod = 'Check'        then 1
             when @paymentmethod = 'Credit card'  then 2
             when @paymentmethod = 'Direct debit' then 3
             when @paymentmethod = 'Stock'        then 4
             when @paymentmethod = 'Property'     then 5
             when @paymentmethod = 'Gift-in-kind' then 6
             when @paymentmethod = 'None'         then 9
             when @paymentmethod = 'Other'        then 10
             else 0
             end;
    
    if 0 < LEN(@ErrorMessages) begin
      --this is going to take us to the catch block, where the error ends up inside @ErrorMessages    
      RAISERROR(@ErrorMessages, 16, 1);
    end;
    
    DECLARE @RevenueID uniqueidentifier = newid();
    INSERT INTO REVENUE (ID, CONSTITUENTID, DATE, POSTDATE, RECEIPTAMOUNT, AMOUNT, DESCRIPTION,  ORGANIZATIONAMOUNT, TRANSACTIONAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DONOTPOST)
    VALUES (@RevenueID, @ConstituentID, @RevenueDate, @PostDate, @Amount, @Amount, @Description, @Amount, @Amount, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 1)

    UPDATE FINANCIALTRANSACTION SET USERDEFINEDID = @LookupID WHERE ID = @RevenueID

    INSERT INTO REVENUERECEIPT (RECEIPTNUMBER, RECEIPTPROCESSDATE, RECEIPTDATE, REVENUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    VALUES(@ReceiptNumber, @ReceiptDate, @ReceiptDate, @RevenueID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    INSERT INTO REVENUEPAYMENTMETHOD(REVENUEID, PAYMENTMETHODCODE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    VALUES (@RevenueId, @PaymentMethodCode, @Amount, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
  
    declare @revenuesplitid uniqueidentifier = newid();
    INSERT INTO REVENUESPLIT (ID, REVENUEID, DESIGNATIONID, AMOUNT, DESCRIPTION, ORGANIZATIONAMOUNT, TRANSACTIONAMOUNT,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    VALUES(@revenuesplitid, @RevenueID, @DesignationID, @Amount, @Description, @Amount, @Amount, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    
    if @PledgeGuid is not null begin
      declare @currencyid uniqueidentifier = (select top 1 id from CURRENCY where ISORGANIZATIONCURRENCY = 1);

      --if it is against a pledge, complicated things can happen.  so lets get messy....
      declare @payment_amount money = @amount;
      declare @remaining_to_distribute money = @payment_amount;

      declare @unfinished_installments table
      (
        id uniqueidentifier
       ,amount_due money
       ,amount_paid money
       ,unpaid_balance money
       ,installment_due_date date
       ,DESIGNATIONID uniqueidentifier
       ,INSTALLMENTSPLITID uniqueidentifier
      );
      insert into @unfinished_installments
      select
        NEWID() as id
       ,inspl.AMOUNT as amount_due
       ,inspl.amount - dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(inspl.id) as amount_paid
       ,inspl.AMOUNT - (inspl.amount - dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(inspl.id)) as unpaid_balance
       ,ins.DATE as installment_due_date
       ,inspl.DESIGNATIONID
       ,inspl.ID as installmentsplitid
      from
        INSTALLMENT ins
        inner join
        INSTALLMENTSPLIT inspl
        on ins.id = inspl.INSTALLMENTID
      where 1=1
        and REVENUEID = @pledgeguid
        and (inspl.AMOUNT - (inspl.amount - dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(inspl.id))) > 0
      order by
        ins.DATE asc

      while 0 < @remaining_to_distribute begin  
        --print 'we still have ' + convert(nvarchar(max),@remaining_to_distribute) + ' to distribute';
        declare @current_unfinished_id uniqueidentifier = (select top 1 id from @unfinished_installments order by installment_due_date);
       
        declare @installmentsplitid uniqueidentifier = (select installmentsplitid from @unfinished_installments where id = @current_unfinished_id);
        
        if (@current_unfinished_id is not null) and (@installmentsplitid is not null) begin
          declare @unpaid_balance money = (select unpaid_balance from @unfinished_installments where id = @current_unfinished_id);
          --amount for this installment will be the lesser of unpaid balance or amount we have to distribute
          declare @amount_to_pay money = case when @unpaid_balance < @remaining_to_distribute then @unpaid_balance else @remaining_to_distribute end;
          --print 'we have an installment available with an unpaid balance of ' + convert(nvarchar(max),@unpaid_balance) + '.';
          --print 'the installment split id is ' + convert(nvarchar(max),@installmentsplitid) + '.';
          --print 'Pay that installment in the amount of ' + convert(nvarchar(max),@amount_to_pay) + '.';

          insert into INSTALLMENTSPLITPAYMENT(ID,PAYMENTID,PLEDGEID,INSTALLMENTSPLITID,AMOUNT,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,OVERPAYMENTAPPLICATIONTYPECODE,APPLICATIONCURRENCYID)
          values(NEWID(),@revenuesplitid,@PledgeGuid,@installmentsplitid,@amount_to_pay,@changeagentid,@changeagentid,@currentdate,@currentdate,1,@currencyid);

          set @remaining_to_distribute = @remaining_to_distribute - @amount_to_pay;
          delete @unfinished_installments where id = @current_unfinished_id;
        end
        else begin
          --print 'we have more money to distribute, but there are no more installments to put it on';
          --print 'CRM does not allow overpayment on pledges, so we have to create a brand new revenue record to hold the overflow';
          --print 'Create a new revenue record in the amount of ' + convert(nvarchar(max),@remaining_to_distribute) + '.';
          set @remaining_to_distribute = 0;
        end;
      end;
    end;
    
  end try
  begin catch
    --don't raise errors; just put them in a text field so that callers can view them
    set @ErrorMessages = ERROR_MESSAGE();
  end catch
  return 0;
end;
]]>
			</c:CreateProcedureSQL>
		</SaveImplementation>
	</SPDataForm>

    <c:FormMetaData FixedDialog="true">
    <c:FormFields>
      <c:FormField FieldID="ConstituentID" Caption="ConstituentID" DataType="Guid" />
      <c:FormField FieldID="RevenueDate" Caption="RevenueDate" DataType="Date" />
      <c:FormField FieldID="PostDate" Caption="PostDate" DataType="Date" />
      <c:FormField FieldID="Amount" Caption="Amount" DataType="Money" />
      <c:FormField FieldID="Description" Caption="Description" DataType="String" MaxLength="255" />
      <c:FormField FieldID="LookupID" Caption="LookupID" DataType="String" MaxLength="255" />
      <c:FormField FieldID="Designation" Caption="Designation" DataType="String" MaxLength="255" />
      <c:FormField FieldID="SubDesignation" Caption="SubDesignation" DataType="String" MaxLength="255" />
      <c:FormField FieldID="ReceiptDate" Caption="ReceiptDate" DataType="Date" />
      <c:FormField FieldID="PaymentMethod" Caption="PaymentMethod" DataType="String" MaxLength="255" />
      <c:FormField FieldID="PledgeId" Caption="PledgeId" DataType="String" MaxLength="255" />
      <c:FormField FieldID="ErrorMessages" Caption="Error Messages" DataType="String" />
    </c:FormFields>
  </c:FormMetaData>
</AddDataFormTemplateSpec>
